The problem with nulls

Boolean logic

Everyone who has worked with SQL has been bitten by nulls. We have our Boolean logic, where statements resolve to either true or false. A null in SQL means we don’t know. The problem is there are many different types of not knowing:

We may have a place holder for a date or value that will come in the future
We may have optional data
We may have added things that matter operationally now but don’t know what to put in old records
I’m sure you can think of some more.

All of these things are modelled with null. Null is a state, not a value. Any operation done with a null returns null, which equates to false in Boolean operations and null in column selections.

It also means, weirdly, that nulls have a type, unlike nil in Object-Oriented programming.

This means our nice clean boolean logic stops being true or false, and becomes true / false / not yet known / optional / things changed / something else we haven’t thought of.

This is why SQL does not support equality between nulls, unknown does not equate to unknown, any operation with unknown is itself unknown, and will be treated as false.

> select null = null ;
+----------+
| ?column? |
|----------|
| <null>   |
+----------+
> select null != null ;
+----------+
| ?column? |
|----------|
| <null>   |
+----------+

This is why SQL has is (not) null as an operator, you have to explicitly say you’re working with a null and what you want to do with it, and then add that to your boolean statements.

You move away from the pleasant ease of using simple true or false and instead have many results from any number of possible reasons. Suddenly there may be any number of meanings to the use of the null. C J Date, author of the classic Introduction to Database Systems, hates nulls because they break Boolean logic so badly.

An example of how nulls can cause problems

Let’s give ourselves a table of People with first_name, last_name and allow last name to be null:

CREATE TABLE people (
    person_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name text NOT NULL,
    last_name text
);

Now let’s whack some data into it:

INSERT INTO people (first_name, last_name)
VALUES 
(
  'First', 'First'
),
(
  'Second', 'Second'
),
(
  'Unknown', NULL
)
;

Now let’s look at some joining queries:

select p1.first_name p1f, p2.first_name p2f
from people p1, people p2
where p1.last_name != p2.last_name
and p1.last_name != 'Second'
;
+-------+--------+
| p1f   | p2f    |
|-------+--------|
| First | Second |
+-------+--------+
SELECT 1

So where is the third (contrived) record?

select p1.first_name p1f, p2.first_name p2f
 from people p1, people p2
 where coalesce(p1.last_name,'ZZZ') != p2.last_name
 and coalesce(p1.last_name,'ZZZ') != 'Second'
 ;
+---------+--------+
| p1f     | p2f    |
|---------+--------|
| Unknown | First  |
| First   | Second |
| Unknown | Second |
|---------+--------|

This is horrible, and very difficult to remember to do for columns that you don’t know are null. Many database designers completely avoid using nulls because it’s so fraught with not getting back what you expect.

Also see:

select * from people where last_name in ('First', null);
+-----------+------------+-----------+
| person_id | first_name | last_name |
|-----------+------------+-----------|
| 1         | First      | First     |
+-----------+------------+-----------+

and

select first_name || ' ' || last_name from people;
+---------------+
| ?column?      |
|---------------|
| First First   |
| Second Second |
| <null>        |
+---------------+

Any operation with null is null.

Sorting nulls

select last_name from people order by last_name;
+-----------+
| last_name |
|-----------|
| First     |
| Second    |
| <null>    |
+-----------+

Ways of avoiding using nulls

One of the strategies proposed is to use 6th Normal Form, which turns attributes that can be nulls into tables in their own right with IDs that point to values. If you want to read up on this then look here (PDF) - in essence nullable columns become references to tables with only keys and values in them. You create a row in that table that the unknown value maps to. It feels very convoluted, and is the database equivalent of the null object pattern.

Some systems use special values or markers to indicate an unknown state, for example a fixed date far in the future for dates that are unknown, or using -1 when an optional ID field is required. This has parallels with some programming practices, it is very common to return -1 when you’ve been looking for the position of something in a zero-indexed array.

Some databases, for example Oracle, store empty strings as nulls and you can’t check to see if a string’s length is greater than zero, because the test will return null.

Indexes

Often nulls are not indexed - this often means that as soon as you do anything with nulls you can end up with full table scans. Sometimes you might find that a query that looks ok to you, creating a small result set for driving another query through an index, suddenly misbehaves because the optimiser gets confused by a null operation. These days, you can get around it by creating indexes that use functions, and those functions can use coalesce to force the row to be indexed.

Keyboard shortcuts